﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MixScript.BLL.FI
{
    public class cAccSubLedger : cBaseBLL
    {

        public bool getSubsidiaryData(ref DataTable dt, string process)
        {

            try
            {
                dt = new DataTable();

                string sSQL = @"SELECT a.acctnum,a.acctname,a.dbcd,a.currency,SUM(l.debet) as debet, SUM(l.kredit) as kredit 
                                FROM accaccounts a LEFT JOIN 
                                (SELECT acctnum, Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                                CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit 
                                FROM accsubledger) AS l ON l.acctnum = a.acctnum  
                                WHERE process = '{0}'
                                GROUP BY a.acctnum ,a.acctname,a.dbcd,a.currency";
              
                dt = Dbserver.ExecuteQuery(string.Format(sSQL, process), "Bsubsidiary");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public bool getAcc(out DataTable dt, string accnum)
        {
            dt = new DataTable();
            string sSQL = @"SELECT *
                                FROM accaccounts 
                                WHERE acctnum = '{0}'";
            dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum), "Bsubsidiary");
            return dt.Rows.Count > 0;
        
        }
        public bool getSubsidiary(ref DataTable dt, string accnum)
        {

            string Sql;
            Sql = @"getSubsidiary '{0}' ";
            try
            {
                dt = Dbserver.ExecuteQuery(String.Format(Sql, accnum), "Subsidiary");
                return true;
            }
            catch (Exception ex)
            { throw ex; }
        }
        public bool getSubSubsidiary(ref DataTable dt, Int32 process, string accnum, string dbcd, string curr)
        {
            try
            {
                dt = new DataTable();
                string sSQL;
                //                sSQL = @"SELECT b.banknum code,b.bankname name,ISNULL(s.process,'{1}') process,ISNULL(s.dbcd,'{2}'),ISNULL(s.currency,'{3}'), 
                //                    ISNULL(s.debet,0) as debet, isnull(s.kredit,0) as kredit
                //                    FROM mst_bank b LEFT JOIN (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                //                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                //                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                //                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}' 
                //                    ) s ON b.banknum=s.subledger ORDER BY b.banknum";

                if (process == 3)
                {
                    sSQL = @"SELECT b.banknum code,b.bankname name,ISNULL(s.process,'{1}') process,ISNULL(s.dbcd,'{2}') dbcd,ISNULL(s.currency,'{3}') currency, 
                    CAST(s.debet AS DECIMAL(10,2)) as debet, CAST(s.kredit AS DECIMAL(10,2)) as kredit
                    FROM mst_bank b LEFT JOIN (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}'
                    ) s ON b.banknum=s.subledger ORDER BY b.banknum";
                    dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum, process, dbcd, curr), "SUB");
                }
                else if (process == 4)
                {
                    sSQL = @"SELECT c.codfbpid code,c.bpidnm name,ISNULL(s.process,'{1}') process,ISNULL(s.dbcd,'{2}') dbcd,ISNULL(s.currency,'{3}') currency, 
                    CAST(s.debet AS DECIMAL(10,2)) as debet, CAST(s.kredit AS DECIMAL(10,2)) as kredit
                    FROM vw_joid_customer_v c LEFT JOIN  (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}'
                    ) s ON c.codfbpid =s.subledger ORDER BY c.bpidnm";
                    dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum, process, dbcd, curr), "SUB");
                }
                else if (process == 6)
                {
                    sSQL = @"SELECT e.nik code,e.nama name,ISNULL(s.process,'{1}') process,ISNULL(s.dbcd,'{2}') dbcd,ISNULL(s.currency,'{3}') currency, 
                    CAST(s.debet AS DECIMAL(10,2)) as debet, CAST(s.kredit AS DECIMAL(10,2)) as kredit
                    FROM tb_employee e LEFT JOIN (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}'
                    ) s ON e.empl_id=s.subledger ORDER BY e.empl_id";
                    dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum, process, dbcd, curr), "SUB");
                }
                else if (process == 7)
                {

                    sSQL = @"SELECT c.codfbpid code,c.bpidnm name,ISNULL(s.process,'{1}') process,ISNULL(s.dbcd,'{2}') dbcd,ISNULL(s.currency,'{3}') currency, 
                    CAST(s.debet AS DECIMAL(10,2)) as debet, CAST(s.kredit AS DECIMAL(10,2)) as kredit
                    FROM vw_joid_supplier_v c LEFT JOIN  (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}'
                    ) s ON c.codfbpid =s.subledger ORDER BY c.bpidnm";

                    dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum, process, dbcd, curr), "SUB");
                }
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        public bool SaveSubSidiary(string acctnum, Int32 process, string Name, string dbc, String value, int id_period)
        {
            try
            {
                string sSQL;
                string sSQL1;
                if (value != "")
                {
                    sSQL = @"DELETE accsubledger where acctnum = '{0}' and subledger ='{1}'";
                    Dbserver.openConn();
                    Dbserver.ExecuteNonQuery(string.Format(sSQL, acctnum.TrimEnd(),Name));
                    Dbserver.CommitAndCloseConn();
                    sSQL1 = @"INSERT INTO accsubledger (kd_cmpy, acctnum, process, subledger, saldodbcd, valconvert,period_id ) 
                    VALUES (1,'{0}','{1}','{2}', '{3}', '{4}','{5}')";


                    Dbserver.openConn();
                    Dbserver.ExecuteNonQuery(string.Format(sSQL1, acctnum.TrimEnd(), process, Name, dbc, value, id_period));
                    Dbserver.CommitAndCloseConn();

                }
                return true;

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

    }
}
